﻿using CK.Sprite.Form.Core;
using CK.Sprite.Framework;
using Dapper;
using Dapper.Contrib.Extensions;
using JetBrains.Annotations;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace CK.Sprite.Form.Business
{
    public class StockRepository : IStockRepository
    {
        public StockRepository(IUnitOfWork unitOfWork)
        {
            _unitOfWork = unitOfWork;
        }

        public IUnitOfWork _unitOfWork { get; private set; }

        public async Task RemoveStockChecksAsync(DateTime checkTime)
        {
            var strSql = $"DELETE FROM StockChecks WHERE CheckTime = @CheckTime;";
            await _unitOfWork.Connection.ExecuteAsync(strSql, new { CheckTime = checkTime.Date });
        }

        public async Task<JObject> GetStockChecks(List<string> fields, DateTime checkTime)
        {
            fields = fields.Select(r => $"SUM(CASE StockType WHEN 'in' THEN {r}	ELSE -{r} END) AS {r}").ToList();
            JObject result = new JObject();
            var realCheckTime = checkTime.Date.AddDays(1).AddSeconds(-1);
            var strSql = $"SELECT {string.Join(",", fields)} FROM StockMgrs WHERE AddTime<@CheckTime;";
            var resultGet = await _unitOfWork.Connection.QueryFirstOrDefaultAsync<dynamic>(strSql, new { CheckTime = realCheckTime });

            result.Add(new JProperty("result", JObject.FromObject(resultGet, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));

            return result;
        }

        public async Task<JObject> GetStockCheckDays(List<string> fields, DateTime checkTime, string stockType)
        {
            fields = fields.Select(r => $"SUM(CASE StockType WHEN 'in' THEN {r}	ELSE -{r} END) AS {r}").ToList();
            JObject result = new JObject();
            var startTime = checkTime.Date;
            var endTime = checkTime.Date.AddDays(1).AddSeconds(-1);
            var realCheckTime = checkTime.Date.AddDays(1).AddSeconds(-1);
            var strSql = $"SELECT {string.Join(",", fields)} FROM StockMgrs WHERE AddTime BETWEEN @StartTime AND @EndTime AND StockType=@StockType;";
            var resultGet = await _unitOfWork.Connection.QueryFirstOrDefaultAsync<dynamic>(strSql, new { StartTime = startTime, EndTime = endTime, StockType = stockType });

            result.Add(new JProperty("result", JObject.FromObject(resultGet, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));
            return result;
        }

        public async Task AddStockChecksAsync(List<string> fields, JObject addData)
        {
            var dynamicFields = string.Join(",", fields);
            var dynamicFieldValues = string.Join(",@", fields);
            var strSql = $"INSERT INTO StockChecks(Id,StockCheckType,CheckTime,{dynamicFields}) VALUES (@Id,@StockCheckType,@CheckTime,@{dynamicFieldValues});";
            await _unitOfWork.Connection.ExecuteAsync(strSql, addData.ToConventionalDotNetObject());
        }
    }
}
